import pandas as pd
import mysql.connector
from mysql.connector import Error


class MySQLConnectionManager:
    def __init__(self, host, user, password, database, port=3306):
        """
        初始化MySQL连接管理器。
        参数:
        - host (str): MySQL服务器主机名或IP地址
        - user (str): 登录用户名
        - password (str): 登录密码
        - database (str): 要连接的数据库名称
        """
        self.host = host
        self.user = user
        self.port = port
        self.password = password
        self.database = database
        self.connection = None
        self.cursor = None

    def connect(self):
        """
        建立到MySQL数据库的连接并创建游标。

        异常:
        - Raises mysql.connector.Error if unable to connect.
        """
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database
            )
            self.cursor = self.connection.cursor(dictionary=True)  # 使用字典模式返回查询结果
        except Error as e:
            print(f"Error connecting to MySQL: {e}")
            raise

    def disconnect(self):
        """
        关闭当前的数据库连接和游标。
        """
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()

    def execute_query(self, query, params=None):
        """
        执行SQL查询。

        参数:
        - query (str): SQL查询语句
        - params (tuple or list, optional): 查询参数，如果查询需要的话

        返回:
        - cursor.fetchall()的结果，即查询结果列表（每一项为字典形式）

        异常:
        - Raises mysql.connector.Error if a database error occurs.
        """
        if not self.cursor or not self.connection.is_connected():
            self.connect()

        try:
            if params:
                self.cursor.execute(query, params)
            else:
                self.cursor.execute(query)
            results = self.cursor.fetchall()
            return results
        except Error as e:
            print(f"Error executing query: {e}")
            raise

    def executeNonQuery(self, query, params=None):
        """
        执行非查询型SQL语句（如INSERT, UPDATE, DELETE等）。

        参数:
        - query (str): SQL非查询语句
        - params (tuple or list, optional): 语句参数，如果语句需要的话

        返回:
        - 受影响的行数

        异常:
        - Raises mysql.connector.Error if a database error occurs.
        """
        if not self.cursor or not self.connection.is_connected():
            self.connect()

        try:
            if params:
                self.cursor.execute(query, params)
            else:
                self.cursor.execute(query)
            self.connection.commit()  # 提交事务
            return self.cursor.rowcount
        except Error as e:
            self.connection.rollback()  # 发生错误时回滚事务
            print(f"Error executing non-query: {e}")
            raise



class ExcelToDatabase:
    def __init__(self, excel_path, connection_manager):
        """
        初始化Excel到数据库转换器。

        参数:
        - excel_path (str): Excel文件路径
        - connection_manager (MySQLConnectionManager): 已经连接的MySQL连接管理器实例
        """
        self.excel_path = excel_path
        self.connection_manager = connection_manager

    def read_excel(self):
        """
        读取指定Excel文件并返回DataFrame。

        返回:
        - pandas DataFrame: 包含Excel数据的DataFrame对象
        """
        return pd.read_excel(self.excel_path)

    def write_to_database(self, dataframe, table_name, if_exists='replace'):
        """
        将DataFrame中的数据写入MySQL数据库的指定表。

        参数:
        - dataframe (pandas DataFrame): 包含待写入数据的DataFrame
        - table_name (str): 目标数据库表名
        - if_exists (str, optional): 指定在目标表已存在时的行为，可选值包括：
          - 'replace': 删除现有表并重新创建（默认）
          - 'append': 在已有数据后追加新数据
          - 'fail': 如果表已存在，则引发异常

        异常:
        - Raises mysql.connector.Error if a database error occurs.
        """
        columns = ', '.join(dataframe.columns.tolist())

        values_placeholders = ', '.join(['%s'] * len(dataframe.columns))

        create_table_sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns})"
        insert_data_sql = f"INSERT INTO {table_name} VALUES ({values_placeholders})"

        self.connection_manager.execute_query(create_table_sql)

        for index, row in dataframe.iterrows():
            row_data = tuple(row.values)
            self.connection_manager.executeNonQuery(insert_data_sql, row_data)

    def process_excel_to_db(self, table_name, if_exists='replace'):
        """
        读取Excel文件并将其内容写入MySQL数据库的指定表。

        参数:
        - table_name (str): 目标数据库表名
        - if_exists (str, optional): 指定在目标表已存在时的行为，同`write_to_database`方法

        异常:
        - Raises mysql.connector.Error if a database error occurs.
        """
        dataframe = self.read_excel()
        self.write_to_database(dataframe, table_name, if_exists=if_exists)


if __name__ == '__main__':
    # 使用示例
    connection_manager = MySQLConnectionManager(
        host='127.0.0.1',
        user='root',
        password='123456',
        database='cmxx',
        port=3307
    )
    connection_manager.connect()
    excel_file_path = "F:\\python_test.xlsx"  # 替换为实际的Excel文件路径
    excel_converter = ExcelToDatabase(excel_file_path, connection_manager)
    excel_converter.process_excel_to_db('target_table_name')

    connection_manager.disconnect()
